oracle判断是否为数字或数字型字符串(sqlserver isnumeric) |
您所在的位置:网站首页 › sql server isnumeric › oracle判断是否为数字或数字型字符串(sqlserver isnumeric) |
需求: 如果字段值不是数字或者数字型字符串,把该字段值update成'' 1.函数法(不能批量,舍弃): create or replace function isNumber(p_in varchar2) return boolean as i number; begin i:=to_number(p_in); return true; exception when others then return false; end ;但是这个好像只能一个个判断,不能批量。 2. SQL> select nvl2(translate('333', '\1234567890 ', '\'), 'is characters ', 'is number ') from dual; NVL2(TRANSLATE('333','\1234567 ------------------------------ is number SQL> select nvl2(translate(333, '\1234567890 ', '\'), 'is characters', 'is number') from dual; NVL2(TRANSLATE(333,'\123456789 ------------------------------ is number SQL> select nvl2(translate('aaaaa', '\1234567890 ', '\'), 'is characters', 'is number') from dual; NVL2(TRANSLATE('AAAAA','\12345 ------------------------------ is characters SQL>3.据说要批量,可以这样先查出来,没有试过: select decode(nvl2(translate(column, '\1234567890 ', '\'), '0', '1') ,'1',column,'') from dual;我写的: Update T_87_Mtbuf Set SpCode='' Where nvl2(translate(SpCode, '\1234567890 ', '\'), 'is characters', 'is number ')='is characters';补充一点: to_number(...)中只能接受数字如122或数字型字符串'122',接受'aaa'会报错“无效字符”。 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |